# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

from .items import AminerItem, ExpertItem
import json
import mysql.connector


class AminerPipeline:

    @classmethod
    def from_crawler(cls, crawler):
        # crawler.settings.get
        db_settings = crawler.settings.get("DATABASE")
        return cls(db_settings)

    def __init__(self, db_settings=None):
        super(AminerPipeline, self).__init__()
        assert db_settings is not None
        self.db_settings = db_settings

    def open_spider(self, spider):
        logger = spider.logger

        logger.info("Aminer pipeline: spider opened")

        self.afile = open("aminer.txt", 'w', encoding="utf8")
        self.efile = open("expert.txt", 'w', encoding="utf8")

        self.db = mysql.connector.connect(**self.db_settings)
        self.cursor = self.db.cursor()

        sql = "SHOW columns FROM `tb_expert`"
        self.cursor.execute(sql)

        self.columns = [column[0] for column in self.cursor.fetchall()]
        logger.info("db table columns: %s", self.columns)

    def close_spider(self, spider):
        logger = spider.logger

        logger.info("Aminer pipeline: spider closed")

        self.afile.close()
        self.efile.close()
        self.cursor.close()
        self.db.close()

    def process_item(self, item, spider):
        if isinstance(item, AminerItem):
            return self.process_aminer_item(item, spider)
        elif isinstance(item, ExpertItem):
            return self.process_expert_item(item, spider)
        return item

    def process_aminer_item(self, item: AminerItem, spider):
        logger = spider.logger

        logger.debug("pipeline aminer items: {}".format(item))

        line = json.dumps(ItemAdapter(item).asdict(), ensure_ascii=False) + "\n"
        self.afile.write(line)

        aminer_id = item['id']
        sql = "select id from `tb_expert` where `aminer_id`=%s"
        self.cursor.execute(sql, (aminer_id, ))
        if self.cursor.fetchone() is not None:
            sql = "UPDATE `tb_expert` SET aminer_id = %s "
            val = [aminer_id]

            for key, value in item.items():
                if key in {"id"} or key not in self.columns:
                    continue
                if type(value) is str and value.strip() == "":
                    continue
                val.append(value)
                sql = sql + ", " + key + " = %s "
            for key, value in item['indices'][0].items():
                if key not in self.columns:
                    continue
                if type(value) is str and value.strip() == "":
                    continue
                val.append(value)
                sql = sql + ", " + key + " = %s "
            for key, value in item['profile'][0].items():
                if key not in self.columns:
                    continue
                if type(value) is str and value.strip() == "":
                    continue
                val.append(value)
                sql = sql + ", " + key + " = %s "
            sql = sql + " where `aminer_id` = '{}' ".format(aminer_id)
            self.cursor.execute(sql, tuple(val))
            self.db.commit()
            logger.info("update %s, %d row affected.", aminer_id, self.cursor.rowcount)
        else:
            sql = "INSERT `tb_expert` (`aminer_id`"
            val = [aminer_id]
            for key, value in item.items():
                if key in {"id"} or key not in self.columns:
                    continue
                if type(value) is str and value.strip() == "":
                    continue
                sql = sql + (", `%s`" % key)
                val.append(value)
            for key, value in item['indices'][0].items():
                if key not in self.columns:
                    continue
                if type(value) is str and value.strip() == "":
                    continue
                sql = sql + (", `%s`" % key)
                val.append(value)
            for key, value in item['profile'][0].items():
                if key not in self.columns:
                    continue
                if type(value) is str and value.strip() == "":
                    continue
                sql = sql + (", `%s`" % key)
                val.append(value)
            sql = sql + ") VALUES (" + (",".join(["%s"] * len(val))) + ")"
            self.cursor.execute(sql, tuple(val))
            self.db.commit()
            logger.info("insert %s, %d row affected, inserted id is %d", aminer_id, self.cursor.rowcount, self.cursor.lastrowid)

    def process_expert_item(self, item: ExpertItem, spider):
        logger = spider.logger

        logger.debug("pipeline expert items: {}".format(item))

        line = json.dumps(ItemAdapter(item).asdict(), ensure_ascii=False) + "\n"
        self.efile.write(line)

        aminer_id = item['id']
        sql = "UPDATE `tb_expert` SET aminer_id = %s "
        val = [aminer_id]

        for key, value in item.items():
            if key in {"id", "tags"} or key not in self.columns:
                continue
            if type(value) is str and value.strip() == "":
                continue
            val.append(value)
            sql = sql + ", " + key + " = %s "
        for key, value in item['profile'][0].items():
            if key not in self.columns:
                continue
            if type(value) is str and value.strip() == "":
                continue
            val.append(value)
            sql = sql + ", " + key + " = %s "
        tags_json = json.dumps([ItemAdapter(tag).asdict() for tag in item['tags']], ensure_ascii=False)
        sql = sql + ", tags = %s "
        val.append(tags_json)

        sql = sql + " where `aminer_id` = '{}' ".format(aminer_id)

        self.cursor.execute(sql, tuple(val))
        self.db.commit()
        logger.info("update %s, %d row affected.", aminer_id, self.cursor.rowcount)


